# -*- coding: utf-8 -*-
"""
Created on Fri Jan  8 16:01:02 2021

@author: PC
"""
import pandas as pd
from sqlalchemy import create_engine
conn=create_engine('mysql+pymysql://root:123456@localhost:3306/lings?charset=utf8')
df=pd.read_sql("select * from tbl_course",conn)
df=df[df['open_time']<'2019-12']
df=df.drop('dependency',axis=1).join(df['dependency'].str.split('/',expand=True).stack().reset_index(level=1,drop=True).rename('dependency'))
print(df)
df=df.drop('dependency',axis=1).join(df['dependency'].str.split('-',expand=True).stack().reset_index(level=1,drop=True).rename('dependency'))
df=df.reset_index()
print(df)
df2=df['hours_per_week'].groupby(df['dependency']).sum()
df1=df['lecturer_id'].groupby(df['dependency']).nunique()
df=pd.merge(df1,df2,how="inner",left_on='dependency',right_on='dependency')
df.columns=['uniqs','total_count']
data=df
df=data.sort_values('uniqs',ascending=False)
print(df)
df.to_csv("C:\\Users\\PC\\Desktop\\2apython.csv")